package com.hs.dnms.action.statics;
 
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.servlet.ModelAndView;

import com.hs.core.action.BaseAction;
import com.hs.core.db.DBMgr;
import com.hs.core.db.QueryPage;
import com.hs.core.utils.PageParam;
import com.hs.core.utils.PageUtil;
import com.hs.core.utils.SystemUtil;
import com.hs.core.utils.TimeUtil;
import com.hs.dnms.model.DnmsDatamine;
import com.hs.dnms.service.IDnmsMagazineService;
@Controller  
public class StaticsController  extends BaseAction {
    /**
	 * 
	 */
	private static final long serialVersionUID = -354434172898440670L;
	private static final Logger logger = Logger.getLogger(StaticsController.class);
	private IDnmsMagazineService magazineService;
	@Autowired  
	public void setRoleService(IDnmsMagazineService magazineService) {
		this.magazineService = magazineService;
	}
	 /**
	  * 文献信息发展趋势统计
	  * @param request
	  * @param response
	  * @param modelMap
	  * @return
	  * @throws ServletException
	  * @throws IOException
	  */
	 @RequestMapping(value="/statics/magadata.do", method = RequestMethod.GET)   
     public ModelAndView magadata(HttpServletRequest request,
				HttpServletResponse response,ModelMap modelMap) throws ServletException, IOException {
         DBMgr dbo = this.getDbo(request);
         String sql = "select * from (select count(*) as countall,PERIODICALNAME from DNMS_MAGAZINE group by PERIODICALNAME) order by countall desc";
         List datalist = dbo.queryList(sql, null);
         modelMap.put("datalist", datalist);
		 return new ModelAndView("app/statics/staticsmaga",modelMap);
		 
	 } 
	 
	 /**
	  * 文献数量趋势统计
	  * @param request
	  * @param response
	  * @param modelMap
	  * @return
	  * @throws ServletException
	  * @throws IOException
	  */
	 @RequestMapping(value="/statics/magacount.do")   
     public ModelAndView magacount(HttpServletRequest request,
				HttpServletResponse response,ModelMap modelMap) throws ServletException, IOException {
         DBMgr dbo = this.getDbo(request);
         String startyear = request.getParameter("startyear");
         String endyear = request.getParameter("endyear");
         request.setAttribute("startyear", startyear);
         request.setAttribute("endyear", endyear);
         String orderName = request.getParameter("orderName");
		 String orderNum = request.getParameter("orderNum");
		 StringBuffer orderByStr = new StringBuffer(" order by ");
		 if(StringUtils.isNotBlank(orderName)){
		    	if("1".equals(orderNum)){
		    		orderByStr.append(orderName).append(" asc");
		    	}
		    	if("2".equals(orderNum)){
		    		orderByStr.append(orderName).append(" desc");
		    	}
		    }else{
		    	orderByStr.append("b.yearstr").append(" desc");
		    	orderName = "b.yearstr";
		    	orderNum = "2";
		    }
		 request.setAttribute("orderName", orderName); 
		 request.setAttribute("orderNum", orderNum); 
         StringBuffer buffer = new StringBuffer("select * from (select * from (select a.yearstr,count(*) as yearcount from (select substr(year,0,4) as yearstr from DNMS_MAGAZINE where year is not null ");
         if(StringUtils.isNotEmpty(startyear)){
        	 buffer.append(" and ");
        	 buffer.append(" substr(year,0,4)>='").append(startyear).append("' ");
         }
         if(StringUtils.isNotEmpty(endyear)){
        	 buffer.append(" and ");
        	 buffer.append(" substr(year,0,4)<='").append(endyear).append("' ");
         }
         buffer.append(") a group by a.yearstr) b ").append(orderByStr).append(")"); 
         List datalist = dbo.queryList(buffer.toString(), null);
         modelMap.put("magalist", datalist);
		 return new ModelAndView("app/statics/magastat",modelMap);
		 
	 } 
	 /**
	  * 期刊数量趋势统计
	  * @param request
	  * @param response
	  * @param modelMap
	  * @return
	  * @throws ServletException
	  * @throws IOException
	  */
	 @RequestMapping(value="/statics/magabookcount.do")   
     public ModelAndView magabookcount(HttpServletRequest request,
				HttpServletResponse response,ModelMap modelMap) throws ServletException, IOException {
		 DBMgr dbo = this.getDbo(request);
         String startyear = request.getParameter("startyear");
         String endyear = request.getParameter("endyear");
         request.setAttribute("startyear", startyear);
         request.setAttribute("endyear", endyear);
         String orderName = request.getParameter("orderName");
		 String orderNum = request.getParameter("orderNum");
		 StringBuffer orderByStr = new StringBuffer(" order by ");
		 if(StringUtils.isNotBlank(orderName)){
		    	if("1".equals(orderNum)){
		    		orderByStr.append(orderName).append(" asc");
		    	}
		    	if("2".equals(orderNum)){
		    		orderByStr.append(orderName).append(" desc");
		    	}
		    }else{
		    	orderByStr.append("yearstr").append(" desc");
		    	orderName = "yearstr";
		    	orderNum = "2";
		    }
		 request.setAttribute("orderName", orderName); 
		 request.setAttribute("orderNum", orderNum); 
         StringBuffer buffer = new StringBuffer("select * from (select yearstr,count(*) as yearcount  from (SELECT   a.yearstr,a.periodicalname  FROM (SELECT SUBSTR (YEAR, 0, 4) AS yearstr, periodicalname   FROM dnms_magazine  WHERE YEAR IS NOT NULL ");
         if(StringUtils.isNotEmpty(startyear)){
        	 buffer.append(" and ");
        	 buffer.append(" substr(year,0,4)>='").append(startyear).append("' ");
         }
         if(StringUtils.isNotEmpty(endyear)){
        	 buffer.append(" and ");
        	 buffer.append(" substr(year,0,4)<='").append(endyear).append("' ");
         }
         buffer.append(") a GROUP BY a.yearstr, a.periodicalname) group by yearstr) ").append(orderByStr); 
         List datalist = dbo.queryList(buffer.toString(), null);
         modelMap.put("magalist", datalist);
		 return new ModelAndView("app/statics/staticsmaga",modelMap);
		 
	 } 
	 /**
	  * 主题词统计
	  * @param request
	  * @param response
	  * @param modelMap
	  * @return
	  * @throws ServletException
	  * @throws IOException
	  */
	 @RequestMapping(value="/statics/keywordstat.do")   
     public ModelAndView keywordstat(HttpServletRequest request,
				HttpServletResponse response,ModelMap modelMap) throws ServletException, IOException {
		 DBMgr dbo = this.getDbo(request); 
		 StringBuffer buffer = new StringBuffer(" select * from DNMS_KEYWORD where COUNTNUM !=0 order by COUNTNUM desc ");
         
          List datalist = dbo.queryList(buffer.toString(), null);
         modelMap.put("keywordlist", datalist);
		 return new ModelAndView("app/statics/keywordstat",modelMap);
		 
	 } 
	 /**
	  * 证候统计
	  * @param request
	  * @param response
	  * @param modelMap
	  * @return
	  * @throws ServletException
	  * @throws IOException
	  */
	 @RequestMapping(value="/statics/symstat.do")   
     public ModelAndView symstat(HttpServletRequest request,
				HttpServletResponse response,ModelMap modelMap) throws ServletException, IOException {
		 DBMgr dbo = this.getDbo(request);
         String startyear = request.getParameter("startyear");
         String endyear = request.getParameter("endyear");
         request.setAttribute("startyear", startyear);
         request.setAttribute("endyear", endyear);
         StringBuffer buffer = new StringBuffer("select * from (  SELECT symptomname FROM (SELECT   * FROM (SELECT   symptomname, COUNT (*) AS symcount FROM dnms_sym WHERE symptomname!='无' and symptomname IS NOT NULL GROUP BY symptomname) a ORDER BY symcount DESC) b) WHERE ROWNUM <= 10 ");

         //buffer.append(") a GROUP BY a.yearstr, a.periodicalname) group by yearstr) order by  yearstr desc");
         String cyear = TimeUtil.getCurrentYear();
         StringBuffer yearbuffer = new StringBuffer("[");
         int curryear = Integer.parseInt(cyear);
         int syear = 0 ;
         int eyear = 0;
         if(StringUtils.isNotEmpty(startyear) && StringUtils.isNotEmpty(endyear)){
        	 syear = Integer.parseInt(startyear);
        	 eyear = Integer.parseInt(endyear);
         }else if (StringUtils.isNotEmpty(startyear) && StringUtils.isEmpty(endyear)){
        	 syear = Integer.parseInt(startyear);
        	 eyear = curryear; 
         }else if(StringUtils.isEmpty(startyear) && StringUtils.isNotEmpty(endyear)){
        	 syear = 1996;
        	 eyear = Integer.parseInt(endyear);
         }else {
        	 syear = 1996;
        	 eyear = curryear;
        	/* for(int k = 30;k>=0;k--){
            	 yearbuffer.append("'").append(curryear-k).append("'");
            	 if(k != 0){
            		 yearbuffer.append(",");
            	 }
             }*/
         }
         for(int i=syear; i<=eyear; i++){
    		 yearbuffer.append("'").append(i).append("'");
        	 if(i != eyear){
        		 yearbuffer.append(",");
        	 }
    	 }
         yearbuffer.append("]");
         String yearbufferstr = yearbuffer.toString();
         System.out.println("******" + startyear + "*********" + yearbufferstr);
         
         List tabdata = new ArrayList();
         Set sortdata = new TreeSet();

         List symlist = dbo.queryList(buffer.toString(), null);
         StringBuffer imagestr = new StringBuffer();
         for(int i =0;i<symlist.size();i++){
        	 Map sym = (Map)symlist.get(i);
        	 String symname = (String)sym.get("SYMPTOMNAME");
        	 StringBuffer countsql = new StringBuffer();
        	 countsql.append("select count(*) as countsym from DNMS_MAGAZINE c where c.id in(select d.indexid from DNMS_DISEASE d");
        	 countsql.append(" where d.id in(select disid from dnms_sym where SYMPTOMNAME=?))");
        	 if(StringUtils.isNotEmpty(startyear)){
        		 countsql.append(" and ");
        		 countsql.append(" substr(c.year,0,4)>='").append(startyear).append("' ");
        	 }
        	 if(StringUtils.isNotEmpty(endyear)){
        		 countsql.append(" and ");
        		 countsql.append(" substr(c.year,0,4)<='").append(endyear).append("' ");
        	 }
        	 List datalist = dbo.queryList(countsql.toString(), new Object[]{symname});
        	 Map countmaop = (Map)datalist.get(0);
        	 String countsym = countmaop.get("countsym")+"";
        	 Map resultmap = new HashMap();
        	 resultmap.put("symname", symname);
        	 resultmap.put("symcount", countsym);
        	 resultmap.put("countsql", countsql.toString());
        	 
        	 tabdata.add(resultmap);
        	 StaObj obj = new StaObj();;
        	 obj.setCountall(countsym);
        	 obj.setCountsql(countsql.toString());
        	 obj.setNameStr(symname); 
        	 sortdata.add(obj);
        	 //获取图形数据
        	 StringBuffer symonsql = new StringBuffer();
        	 symonsql.append("select * from (select yearstr,count(*) as yearcount from (select substr(c.year,0,4) as yearstr ");
        	 symonsql.append(" from DNMS_MAGAZINE c where c.id in(select d.indexid from DNMS_DISEASE d ");
        	 symonsql.append(" where  d.id in(select disid from dnms_sym where SYMPTOMNAME=?)) ");
        	 if(StringUtils.isNotEmpty(startyear)){
        		 symonsql.append(" and ");
        		 symonsql.append(" substr(c.year,0,4)>='").append(startyear).append("' ");
        	 }
        	 if(StringUtils.isNotEmpty(endyear)){
        		 symonsql.append(" and ");
        		 symonsql.append(" substr(c.year,0,4)<='").append(endyear).append("' ");
        	 }
        	 symonsql.append(" ) a group by yearstr) f order by yearstr");
        	 
        	 List symone = dbo.queryList(symonsql.toString(), new Object[]{symname});
        	 
        	 imagestr.append("{");
        	 imagestr.append("name:'").append(symname).append("',");
        	 imagestr.append("data:[");
        	 for(int k=syear; k<=eyear; k++){
            	 String now = String.valueOf(k);
            	 boolean ishave = false;
            	 String tempcount = "";
            	 for(int h = 0;h<symone.size();h++){
            		 Map symimage = (Map)symone.get(h);
            		 String yearcount = symimage.get("yearcount") + "";
            		 String yearstr =  symimage.get("yearstr")+"";
            		 if(now.equals(yearstr)){
            			 ishave = true ;
            			 tempcount = yearcount;
            			 break;
            		 }
            	 }
            	 if(ishave){
            		 imagestr.append(tempcount);
            	 }else{
            		 imagestr.append('0');
            	 }
             	 if(k != eyear){
            		 imagestr.append(",");
            	 }	 
        	 }
        	 imagestr.append("]");
        	 imagestr.append("}");
        	 if(i != symlist.size()-1){
    			 imagestr.append(",");
    		 }
          }
         modelMap.put("imgdata", imagestr.toString());
         modelMap.put("yearstr", yearbuffer.toString());
         modelMap.put("tabdata", tabdata);
         modelMap.put("sortdata", sortdata);
         String info = getStaticStr(request,"select indexid from DNMS_DISEASE where id in(select disid from DNMS_SYM where SYMPTOMNAME is not null and SYMPTOMNAME !='无') group by indexid  ","证候");
         modelMap.put("allinfo", info);
		 return new ModelAndView("app/statics/symstat",modelMap);
		 
	 } 
	 /**
	  * 方剂统计
	  * @param request
	  * @param response
	  * @param modelMap
	  * @return
	  * @throws ServletException
	  * @throws IOException
	  */
	 @RequestMapping(value="/statics/prestat.do")   
     public ModelAndView prestat(HttpServletRequest request,
				HttpServletResponse response,ModelMap modelMap) throws ServletException, IOException {
		 DBMgr dbo = this.getDbo(request);
         String startyear = request.getParameter("startyear");
         String endyear = request.getParameter("endyear");
         request.setAttribute("startyear", startyear);
         request.setAttribute("endyear", endyear);
         StringBuffer buffer = new StringBuffer("select * from (  SELECT prename FROM (SELECT * FROM (SELECT prename, COUNT (*) AS precount FROM dnms_pre WHERE prename IS NOT NULL and prename!='无' GROUP BY prename) a ORDER BY precount DESC) b) WHERE ROWNUM <= 10 ");
         StringBuffer buffer1 = new StringBuffer("select * from (  SELECT FROMPRENAME FROM (SELECT * FROM (SELECT FROMPRENAME, COUNT (*) AS precount FROM dnms_pre WHERE FROMPRENAME IS NOT NULL   GROUP BY FROMPRENAME) a ORDER BY precount DESC) b) WHERE ROWNUM <= 5 ");

         String cyear = TimeUtil.getCurrentYear();
         StringBuffer yearbuffer = new StringBuffer("[");
         int curryear = Integer.parseInt(cyear);
         int syear = 0 ;
         int eyear = 0;
         if(StringUtils.isNotEmpty(startyear) && StringUtils.isNotEmpty(endyear)){
        	 syear = Integer.parseInt(startyear);
        	 eyear = Integer.parseInt(endyear);
         }else if (StringUtils.isNotEmpty(startyear) && StringUtils.isEmpty(endyear)){
        	 syear = Integer.parseInt(startyear);
        	 eyear = curryear;
         }else if(StringUtils.isEmpty(startyear) && StringUtils.isNotEmpty(endyear)){
        	 syear = 1996;
        	 eyear = Integer.parseInt(endyear);
         }else {
        	 syear = 1996;
        	 eyear = curryear;
         }
         for(int i=syear; i<=eyear; i++){
    		 yearbuffer.append("'").append(i).append("'");
        	 if(i != eyear){
        		 yearbuffer.append(",");
        	 }
    	 }
         yearbuffer.append("]");
         String yearbufferstr = yearbuffer.toString();
         System.out.println("******" + startyear + "*********" + yearbufferstr);
         Set sortdata = new TreeSet();

         List tabdata = new ArrayList();
         List symlist = dbo.queryList(buffer.toString(), null);
         List fromlist = dbo.queryList(buffer1.toString(), null);
         Set fromsortdata = new TreeSet();
         for(int i =0;i<fromlist.size();i++){
        	 Map frommap = (Map)fromlist.get(i);
        	 String fromname = (String)frommap.get("FROMPRENAME");
        	 StringBuffer countsql = new StringBuffer();
        	 countsql.append("SELECT   COUNT ( * ) AS countpre FROM   dnms_magazine c where id in(");
        	 countsql.append(" select d.indexid from dnms_disease d,dnms_sym s where d.id=s.disid and  s.id in ");
        	 
        	 countsql.append(" (select symid from dnms_pre where FROMPRENAME =? ))");
        	 if(StringUtils.isNotEmpty(startyear)){
        		 countsql.append(" and ");
        		 countsql.append(" substr(c.year,0,4)>='").append(startyear).append("' ");
        	 }
        	 if(StringUtils.isNotEmpty(endyear)){
        		 countsql.append(" and ");
        		 countsql.append(" substr(c.year,0,4)<='").append(endyear).append("' ");
        	 }
        	 List datalist = dbo.queryList(countsql.toString(), new Object[]{fromname});
        	 Map countmaop = (Map)datalist.get(0);
        	 String countfrom = countmaop.get("countpre")+"";
        	 StaObj obj = new StaObj();;
        	 obj.setCountall(countfrom);
        	 obj.setCountsql(countsql.toString());
        	 obj.setNameStr(fromname); 
        	 fromsortdata.add(obj);
         }
         StringBuffer imagestr = new StringBuffer();
         for(int i =0;i<symlist.size();i++){
        	 Map sym = (Map)symlist.get(i);
        	 String symname = (String)sym.get("prename");
        	 StringBuffer countsql = new StringBuffer();
        	 countsql.append("SELECT   COUNT ( * ) AS countpre FROM   dnms_magazine c where id in(");
        	 countsql.append(" select d.indexid from dnms_disease d,dnms_sym s where d.id=s.disid and  s.id in ");
        	 
        	 countsql.append(" (select symid from dnms_pre where prename =? ))");
        	 if(StringUtils.isNotEmpty(startyear)){
        		 countsql.append(" and ");
        		 countsql.append(" substr(c.year,0,4)>='").append(startyear).append("' ");
        	 }
        	 if(StringUtils.isNotEmpty(endyear)){
        		 countsql.append(" and ");
        		 countsql.append(" substr(c.year,0,4)<='").append(endyear).append("' ");
        	 }
        	 List datalist = dbo.queryList(countsql.toString(), new Object[]{symname});
        	 Map countmaop = (Map)datalist.get(0);
        	 String countsym = countmaop.get("countpre")+"";
        	 Map resultmap = new HashMap();
        	 resultmap.put("symname", symname);
        	 resultmap.put("symcount", countsym);
        	 resultmap.put("countsql", countsql.toString());
        	 StaObj obj = new StaObj();;
        	 obj.setCountall(countsym);
        	 obj.setCountsql(countsql.toString());
        	 obj.setNameStr(symname); 
        	 sortdata.add(obj);
        	 tabdata.add(resultmap);
        	 //获取图形数据
        	 StringBuffer symonsql = new StringBuffer();
        	 symonsql.append("select * from (select yearstr,count(*) as yearcount from (select substr(c.year,0,4) as yearstr ");
        	 symonsql.append("FROM DNMS_MAGAZINE c where c.id in(select d.indexid from DNMS_DISEASE d,dnms_sym s");
        	 symonsql.append(" WHERE d.id=s.disid and s.id in(select symid from dnms_pre where prename=?))");
        	 if(StringUtils.isNotEmpty(startyear)){
        		 symonsql.append(" and ");
        		 symonsql.append(" substr(c.year,0,4)>='").append(startyear).append("' ");
        	 }
        	 if(StringUtils.isNotEmpty(endyear)){
        		 symonsql.append(" and ");
        		 symonsql.append(" substr(c.year,0,4)<='").append(endyear).append("' ");
        	 }
        	 symonsql.append(") a group by yearstr) f order by yearstr ");
        	 
        	 List symone = dbo.queryList(symonsql.toString(), new Object[]{symname});
        	 
        	 imagestr.append("{");
        	 imagestr.append("name:'").append(symname).append("',");
        	 imagestr.append("data:[");
        	 for(int k=syear; k<=eyear; k++){
            	 String now = String.valueOf(k);
            	 boolean ishave = false;
            	 String tempcount = "";
            	 for(int h = 0;h<symone.size();h++){
            		 Map symimage = (Map)symone.get(h);
            		 String yearcount = symimage.get("yearcount") + "";
            		 String yearstr =  symimage.get("yearstr")+"";
            		 if(now.equals(yearstr)){
            			 ishave = true ;
            			 tempcount = yearcount;
            			 break;
            		 }
            	 }
            	 if(ishave){
            		 imagestr.append(tempcount);
            	 }else{
            		 imagestr.append('0');
            	 }
             	 if(k != eyear){
            		 imagestr.append(",");
            	 }	 
        	 }
        	 imagestr.append("]");
        	 imagestr.append("}");
        	 if(i != symlist.size()-1){
    			 imagestr.append(",");
    		 }
          }
         modelMap.put("imgdata", imagestr.toString());
         modelMap.put("yearstr", yearbuffer.toString());
         modelMap.put("tabdata", tabdata);
         modelMap.put("sortdata", sortdata);
         modelMap.put("fromsortdata", fromsortdata);
         String info = getStaticStr(request,"select indexid from DNMS_DISEASE where id in(SELECT DISID FROM DNMS_SYM WHERE ID IN( SELECT SYMID  FROM DNMS_PRE WHERE PRENAME is not null and PRENAME !='无')) GROUP BY INDEXID","方剂");
         modelMap.put("allinfo", info);

		 return new ModelAndView("app/statics/prestat",modelMap);
		 
	 } 
	 /**
	  * 中药统计
	  * @param request
	  * @param response
	  * @param modelMap
	  * @return
	  * @throws ServletException
	  * @throws IOException
	  */
	 @RequestMapping(value="/statics/herbstat.do")   
     public ModelAndView herbstat(HttpServletRequest request,
				HttpServletResponse response,ModelMap modelMap) throws ServletException, IOException {
		 DBMgr dbo = this.getDbo(request);
         String startyear = request.getParameter("startyear");
         String endyear = request.getParameter("endyear");
         request.setAttribute("startyear", startyear);
         request.setAttribute("endyear", endyear);
         StringBuffer buffer = new StringBuffer("select * from (  SELECT herbname FROM (SELECT   * FROM (SELECT   herbname, COUNT (*) AS herbcount FROM dnms_herb WHERE COUNTUNIT=2 and herbname IS NOT NULL GROUP BY herbname) a ORDER BY herbcount DESC) b) WHERE ROWNUM <= 10 ");
         String cyear = TimeUtil.getCurrentYear();
         StringBuffer yearbuffer = new StringBuffer("[");
         int curryear = Integer.parseInt(cyear);
         int syear = 0 ;
         int eyear = 0;
         if(StringUtils.isNotEmpty(startyear) && StringUtils.isNotEmpty(endyear)){
        	 syear = Integer.parseInt(startyear);
        	 eyear = Integer.parseInt(endyear);
         }else if (StringUtils.isNotEmpty(startyear) && StringUtils.isEmpty(endyear)){
        	 syear = Integer.parseInt(startyear);
        	 eyear = curryear;
         }else if(StringUtils.isEmpty(startyear) && StringUtils.isNotEmpty(endyear)){
        	 syear = 1996;
        	 eyear = Integer.parseInt(endyear);
         }else {
        	 syear = 1996;
        	 eyear = curryear;
         }
         for(int i=syear; i<=eyear; i++){
    		 yearbuffer.append("'").append(i).append("'");
        	 if(i != eyear){
        		 yearbuffer.append(",");
        	 }
    	 }
         yearbuffer.append("]");
         String yearbufferstr = yearbuffer.toString();
         System.out.println("******" + startyear + "*********" + yearbufferstr);
         
        // List tabdata = new ArrayList();
         Set sortdata = new TreeSet();
         List symlist = dbo.queryList(buffer.toString(), null);
         StringBuffer imagestr = new StringBuffer();
         int counter = 0;
         for(int i =0;i<symlist.size();i++){
        	 Map sym = (Map)symlist.get(i);
        	 String symname = (String)sym.get("herbname");
        	 //判断该中药名称是否为正名，如果为正名则查询该药名对应的所有异名作为下一步查询的条件
        	 List herblist = dbo.queryList("select * from DNMS_STANDARD where STANDARDNAME=?", new Object[]{symname});
        	 StringBuffer herbstr = new StringBuffer();
        	 if(null != herblist && herblist.size()>0){
        		 herbstr.append("(").append("'").append(symname).append("'");
        		 for(int m=0;m<herblist.size();m++){
        			 herbstr.append(",");
        			 Map std = (Map)herblist.get(m);
        			 String temp = (String)std.get("SYNONYMS");
        			 herbstr.append("'").append(temp).append("'");
        		 }
        		 herbstr.append(")");
        		 System.out.println("herbstr:" + herbstr);
        	 }else{
        		 continue;
        	 }
        	 counter++;
        	 if(counter >10)
        		 break;
        	 
        	 StringBuffer countsql = new StringBuffer();
        	 countsql.append("SELECT COUNT (*) AS countherb FROM   DNMS_MAGAZINE c where c.id in( ");
        	 countsql.append("select d.indexid from DNMS_DISEASE d,dnms_sym s,dnms_pre p  ");
        	 countsql.append("WHERE d.id=s.disid and s.id=p.symid");
        	 countsql.append(" AND p.id IN (SELECT preid FROM dnms_herb WHERE herbname in"+herbstr+"))");
        	 if(StringUtils.isNotEmpty(startyear)){
        		 countsql.append(" and ");
        		 countsql.append(" substr(c.year,0,4)>='").append(startyear).append("' ");
        	 }
        	 if(StringUtils.isNotEmpty(endyear)){
        		 countsql.append(" and ");
        		 countsql.append(" substr(c.year,0,4)<='").append(endyear).append("' ");
        	 }
        	 List datalist = dbo.queryList(countsql.toString(), null);
        	 Map countmaop = (Map)datalist.get(0);
        	 String countsym = countmaop.get("countherb")+"";
//        	 Map resultmap = new HashMap();
//        	 resultmap.put("symname", symname);
//        	 resultmap.put("symcount", countsym);
//        	 resultmap.put("countsql", countsql.toString());
        	 
//        	 String sqlMax = "select to_char(max(to_number(HERBCOUNT))) as maxstr from dnms_herb where herbname=? and COUNTUNIT=2";
//        	 String sqlMin = "select to_char(min(to_number(HERBCOUNT))) as minstr from dnms_herb where herbname=? and COUNTUNIT=2";
        	 String sqlMax = "select to_char(max(countnum)) as maxstr from dnms_herb where herbname in"+herbstr+" and COUNTUNIT=2 and countnum is not null";
        	 String sqlMin = "select to_char(min(countnum)) as minstr from dnms_herb where herbname in"+herbstr+" and COUNTUNIT=2 and countnum>0 and countnum is not null";
             List maxlist = dbo.queryList(sqlMax, null);
             List minlist = dbo.queryList(sqlMin, null);
        	 StaObj obj = new StaObj();;
        	 obj.setCountall(countsym);
        	 String temp = countsql.toString().replaceAll("'", "&");
        	 System.out.println("temp:" + temp);
        	 obj.setCountsql(temp);
        	 obj.setNameStr(symname);
        	 if(maxlist.size()>0){
        		 Map maxmap = (Map)maxlist.get(0);
        		 String maxstr =(String)maxmap.get("maxstr");
        		 obj.setMaxstr(maxstr);
        	 }
        	 if(minlist.size()>0){
        		 Map minmap = (Map)minlist.get(0);
        		 String minstr =(String)minmap.get("minstr");
        		 obj.setMinstr(minstr);
        	 }
        	 sortdata.add(obj);
        	// tabdata.add(resultmap);
        	 //获取图形数据
        	 StringBuffer symonsql = new StringBuffer();
        	 symonsql.append("select * from (select yearstr,count(*) as yearcount from (select substr(c.year,0,4) as yearstr ");
        	 symonsql.append("from DNMS_MAGAZINE c where c.id in(select d.indexid from DNMS_DISEASE d,dnms_sym s,dnms_pre p ");
        	 symonsql.append(" where d.id=s.disid and s.id=p.symid");
        	 symonsql.append(" and p.id in(select preid from dnms_herb where herbname in"+herbstr+"))");
        	 if(StringUtils.isNotEmpty(startyear)){
        		 symonsql.append(" and ");
        		 symonsql.append(" substr(c.year,0,4)>='").append(startyear).append("' ");
        	 }
        	 if(StringUtils.isNotEmpty(endyear)){
        		 symonsql.append(" and ");
        		 symonsql.append(" substr(c.year,0,4)<='").append(endyear).append("' ");
        	 }
        	 symonsql.append(" ) a group by yearstr) f order by yearstr");
        	 
        	 List symone = dbo.queryList(symonsql.toString(), null);
        	 
        	 imagestr.append("{");
        	 imagestr.append("name:'").append(symname).append("',");
        	 imagestr.append("data:[");
        	 for(int k=syear; k<=eyear; k++){
            	 String now = String.valueOf(k);
            	 boolean ishave = false;
            	 String tempcount = "";
            	 for(int h = 0;h<symone.size();h++){
            		 Map symimage = (Map)symone.get(h);
            		 String yearcount = symimage.get("yearcount") + "";
            		 String yearstr =  symimage.get("yearstr")+"";
            		 if(now.equals(yearstr)){
            			 ishave = true ;
            			 tempcount = yearcount;
            			 break;
            		 }
            	 }
            	 if(ishave){
            		 imagestr.append(tempcount);
            	 }else{
            		 imagestr.append('0');
            	 }
             	 if(k != eyear){
            		 imagestr.append(",");
            	 }	 
        	 }
        	 imagestr.append("]");
        	 imagestr.append("}");
        	 if(i != symlist.size()-1){
    			 imagestr.append(",");
    		 }
          }
         modelMap.put("imgdata", imagestr.toString());
         modelMap.put("yearstr", yearbuffer.toString());
//         modelMap.put("tabdata", tabdata);
         modelMap.put("sortdata", sortdata);
         String info = getStaticStr(request,"select indexid from DNMS_DISEASE where id in(SELECT DISID FROM DNMS_SYM WHERE ID IN(SELECT SYMID FROM DNMS_PRE WHERE ID IN( SELECT PREID  FROM DNMS_HERB WHERE HERBNAME is not null and HERBNAME !='无'))) GROUP BY INDEXID","中药");
         modelMap.put("allinfo", info);
         System.out.println("==================================***");
		 return new ModelAndView("app/statics/herbstat",modelMap);
	 } 
	 /**
	  * 穴位统计
	  * @param request
	  * @param response
	  * @param modelMap
	  * @return
	  * @throws ServletException
	  * @throws IOException
	  */
	 @RequestMapping(value="/statics/pointstat.do")   
     public ModelAndView pointstat(HttpServletRequest request,
				HttpServletResponse response,ModelMap modelMap) throws ServletException, IOException {
		 DBMgr dbo = this.getDbo(request);
         String startyear = request.getParameter("startyear");
         String endyear = request.getParameter("endyear");
         request.setAttribute("startyear", startyear);
         request.setAttribute("endyear", endyear);
         StringBuffer buffer = new StringBuffer("select * from (  SELECT pointname FROM (SELECT   * FROM (SELECT   pointname, COUNT (*) AS pointcount FROM dnms_point WHERE pointname IS NOT NULL GROUP BY pointname) a ORDER BY pointcount DESC) b) WHERE ROWNUM <= 10 ");
         String cyear = TimeUtil.getCurrentYear();
         StringBuffer yearbuffer = new StringBuffer("[");
         int curryear = Integer.parseInt(cyear);
         int syear = 0 ;
         int eyear = 0;
         if(StringUtils.isNotEmpty(startyear) && StringUtils.isNotEmpty(endyear)){
        	 syear = Integer.parseInt(startyear);
        	 eyear = Integer.parseInt(endyear);
         }else if (StringUtils.isNotEmpty(startyear) && StringUtils.isEmpty(endyear)){
        	 syear = Integer.parseInt(startyear);
        	 eyear = curryear;
         }else if(StringUtils.isEmpty(startyear) && StringUtils.isNotEmpty(endyear)){
        	 syear = 1996;
        	 eyear = Integer.parseInt(endyear);
         }else {
        	 syear = 1996;
        	 eyear = curryear;
         }
         for(int i=syear; i<=eyear; i++){
    		 yearbuffer.append("'").append(i).append("'");
        	 if(i != eyear){
        		 yearbuffer.append(",");
        	 }
    	 }
         yearbuffer.append("]");
         String yearbufferstr = yearbuffer.toString();
         System.out.println("******" + startyear + "*********" + yearbufferstr);
         
         List tabdata = new ArrayList();
         Set sortdata = new TreeSet();

         List symlist = dbo.queryList(buffer.toString(), null);
         StringBuffer imagestr = new StringBuffer();
         for(int i =0;i<symlist.size();i++){
        	 Map sym = (Map)symlist.get(i);
        	 String symname = (String)sym.get("pointname");
        	 StringBuffer countsql = new StringBuffer();
        	 countsql.append(" SELECT COUNT (*) AS countherb FROM DNMS_MAGAZINE c where c.id in( ");
        	 countsql.append(" select d.indexid from  DNMS_DISEASE d,dnms_sym s,dnms_pointgroup p ");
        	 countsql.append(" WHERE d.id=s.disid and s.id=p.symid AND p.id IN (SELECT pgroupid FROM dnms_point WHERE pointname =?)) ");
        	 if(StringUtils.isNotEmpty(startyear)){
        		 countsql.append(" and ");
        		 countsql.append(" substr(c.year,0,4)>='").append(startyear).append("' ");
        	 }
        	 if(StringUtils.isNotEmpty(endyear)){
        		 countsql.append(" and ");
        		 countsql.append(" substr(c.year,0,4)<='").append(endyear).append("' ");
        	 }
        	 
        	 List datalist = dbo.queryList(countsql.toString(), new Object[]{symname});
        	 Map countmaop = (Map)datalist.get(0);
        	 String countsym = countmaop.get("countherb")+"";
        	 Map resultmap = new HashMap();
        	 resultmap.put("symname", symname);
        	 resultmap.put("symcount", countsym);
        	 resultmap.put("countsql", countsql.toString());
        	 tabdata.add(resultmap);
        	 StaObj obj = new StaObj();;
        	 obj.setCountall(countsym);
        	 obj.setCountsql(countsql.toString());
        	 obj.setNameStr(symname); 
        	 sortdata.add(obj);
        	 //获取图形数据
        	 StringBuffer symonsql = new StringBuffer();
        	 symonsql.append("select * from (select yearstr,count(*) as yearcount from (select substr(c.year,0,4) as yearstr ");
        	 symonsql.append("from DNMS_MAGAZINE c where c.id in(select d.indexid from DNMS_DISEASE d,dnms_sym s,dnms_pointgroup p");
        	 symonsql.append(" where d.id=s.disid and s.id=p.symid and p.id in(select pgroupid from dnms_point where pointname=?))");
        	 if(StringUtils.isNotEmpty(startyear)){
        		 symonsql.append(" and ");
        		 symonsql.append(" substr(c.year,0,4)>='").append(startyear).append("' ");
        	 }
        	 if(StringUtils.isNotEmpty(endyear)){
        		 symonsql.append(" and ");
        		 symonsql.append(" substr(c.year,0,4)<='").append(endyear).append("' ");
        	 }
        	 symonsql.append(" ) a group by yearstr) f order by yearstr ");
        	 
        	 List symone = dbo.queryList(symonsql.toString(), new Object[]{symname});
        	 
        	 imagestr.append("{");
        	 imagestr.append("name:'").append(symname).append("',");
        	 imagestr.append("data:[");
        	 for(int k=syear; k<=eyear; k++){
            	 String now = String.valueOf(k);
            	 boolean ishave = false;
            	 String tempcount = "";
            	 for(int h = 0;h<symone.size();h++){
            		 Map symimage = (Map)symone.get(h);
            		 String yearcount = symimage.get("yearcount") + "";
            		 String yearstr =  symimage.get("yearstr")+"";
            		 if(now.equals(yearstr)){
            			 ishave = true ;
            			 tempcount = yearcount;
            			 break;
            		 }
            	 }
            	 if(ishave){
            		 imagestr.append(tempcount);
            	 }else{
            		 imagestr.append('0');
            	 }
             	 if(k != eyear){
            		 imagestr.append(",");
            	 }	 
        	 }
        	 imagestr.append("]");
        	 imagestr.append("}");
        	 if(i != symlist.size()-1){
    			 imagestr.append(",");
    		 }
          }
         modelMap.put("imgdata", imagestr.toString());
         modelMap.put("yearstr", yearbuffer.toString());
         modelMap.put("tabdata", tabdata);
         modelMap.put("sortdata", sortdata);

         String info = getStaticStr(request,"select indexid from DNMS_DISEASE where id in(SELECT DISID FROM DNMS_SYM WHERE ID IN(SELECT SYMID FROM DNMS_POINTGROUP WHERE ID IN( SELECT PGROUPID  FROM DNMS_POINT WHERE POINTNAME is not null and POINTNAME !='无'))) GROUP BY INDEXID","穴位");
         modelMap.put("allinfo", info);
		 return new ModelAndView("app/statics/pointstat",modelMap);
	 } 
	 
	 /**
	  * 数据挖掘
	  * @param request
	  * @param response
	  * @param modelMap
	  * @return
	  * @throws ServletException
	  * @throws IOException
	  */
	 @RequestMapping(value="/statics/minedata.do")   
     public ModelAndView minedata(HttpServletRequest request,
				HttpServletResponse response,ModelMap modelMap) throws ServletException, IOException {
		 PageParam pageParam = new  PageParam(request,"statics/minedata.do");
		 PageUtil pageUtil = null;
		 String orderName = request.getParameter("orderName");
		 String orderNum = request.getParameter("orderNum");
		 StringBuffer orderByStr = new StringBuffer(" order by ");
		 logger.debug("orderName:" + orderName);
		 if(StringUtils.isNotBlank(orderName)){
		    	if("1".equals(orderNum)){
		    		orderByStr.append(orderName).append(" asc");
		    	}
		    	if("2".equals(orderNum)){
		    		orderByStr.append(orderName).append(" desc");
		    	}
		    }else{
		    	orderByStr.append("a.content");
		    }
		 request.setAttribute("orderName", orderName);
		 request.setAttribute("orderNum", orderNum);

		 StringBuffer searchBuffer = new StringBuffer(" select a from DnmsDatamine a where 1=1  ");
		 String content = request.getParameter("content");  
		 String datatype = request.getParameter("datatype");  
			request.setAttribute("category", content);
			request.setAttribute("datatype", datatype);
			if(StringUtils.isNotBlank(content)){
				pageParam.getConditionMap().put("content", content);
				searchBuffer.append(" and (a.content like '%"+content+"%') "); 
			}  
			if(StringUtils.isNotBlank(datatype) && !"0".equals(datatype)){
				pageParam.getConditionMap().put("datatype", datatype);
				searchBuffer.append(" and (a.datatype ='"+datatype+"') "); 
			}
			//searchBuffer.append(" order by a.content");
			searchBuffer.append(orderByStr.toString());
			logger.debug("orderByStr:" + orderByStr);
			Map data = new HashMap();
			pageParam.setSearchSql(searchBuffer.toString());
			pageUtil = magazineService.getList(pageParam);
			data.put("dataList", pageUtil);     
 		 return new ModelAndView("app/statics/staticsmine",data);
		 
	 } 
	 /**
	  * 数据挖掘框架
	  * @param request
	  * @param response
	  * @param modelMap
	  * @return
	  * @throws ServletException
	  * @throws IOException
	  */
	 @RequestMapping(value="/statics/minedataFrame.do", method = RequestMethod.GET)   
     public ModelAndView minedataFrame(HttpServletRequest request,
				HttpServletResponse response,ModelMap modelMap) throws ServletException, IOException {
		 return new ModelAndView("app/statics/staticsmineframe",modelMap);
	 }
	 /**
	  * 数据导入
	  * @param request
	  * @param response
	  * @param modelMap
	  * @return
	 * @throws Exception 
	  */
	 @RequestMapping(value="/statics/importdata.do")   
     public ModelAndView importXlsData(HttpServletRequest request,
				HttpServletResponse response,ModelMap modelMap) throws Exception {
		 MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;   
		 MultipartFile multipartFile = multipartRequest.getFile("importfile");   
		 String fileName = multipartFile.getOriginalFilename(); 
		 String webRoot = SystemUtil.getWebappRoot().toString();
		 webRoot = webRoot.replace("\\", "/") + "/temp";
		 System.out.println("webRoot::" + webRoot );
		 String sep = System.getProperty("file.separator");
		 sep = "/"; 
		 File rootDir = new File(webRoot); 
		 if (!rootDir.exists()) {
			rootDir.mkdirs(); 
		 }
		 File file = new File(webRoot + sep + fileName);
		 BufferedOutputStream bf = new BufferedOutputStream(new FileOutputStream(file));
		 bf.write(multipartFile.getBytes());
		 bf.close();
		 System.out.println("上传成功。。。。。。");
		 this.readXls(file);
	     response.getWriter().write("1");
		 return new ModelAndView("app/statics/staticsmine",modelMap);
	 }
	 /**
	  * 读取excel文件
	  * @param file 要读取的excel文件
	  */
	 public void readXls(File file){
			HSSFWorkbook workbook = null;
			try {
				FileInputStream rs = new FileInputStream(file) ;      
				workbook = new HSSFWorkbook(rs);
				int total = workbook.getNumberOfSheets(); //获取sheet个数      
		        int start = 0;
		        int end = 0 ;  
				for(int i = 0 ; i < total ; i ++){  
		            HSSFSheet sheet = workbook.getSheetAt(i);// 按索引获取sheet引用          
		            System.out.println(sheet.getSheetName());      
		            start = sheet.getFirstRowNum();  //sheet起始行索引      
		            end = sheet.getLastRowNum();//sheet起结束行索引      
		            int temp = 1;      
		            HSSFRow row = null ;      
		            HSSFCell concent = null ;      
		            HSSFCell zhichidu = null ; 
		            HSSFCell zhixindu = null ;
		            HSSFCell datatype = null ;
		            HSSFCell memo = null ; 
		            
		            while(temp <= end){      
		            	 DnmsDatamine sta = new DnmsDatamine();
		                 row =  sheet.getRow(temp);//获取第N行表格数据     
		                 if(row != null){
		                	 concent = row.getCell(1);//获取指定列单元格            
		                	 zhichidu = row.getCell(2);   
		                	 zhixindu = row.getCell(3);
		                	 datatype = row.getCell(4);
		                	 memo = row.getCell(5);
				               
				                 if(null != concent){
				                	 sta.setContent(getCell(concent)); 
				                 }
				                 if(null != zhichidu){
				                	 sta.setZhichidu(Double.parseDouble(getCell(zhichidu))); 
				                 }
				                 if(null != zhixindu){
				                	 sta.setZhixingdu(Double.parseDouble(getCell(zhixindu))); 
				                 }
				                 if(null != datatype){
				                	 String dtype = getCell(datatype);
				                	 if("中药对".equals(dtype)){
				                		 sta.setDatatype(1);
				                	 }else if("穴位对".equals(dtype)){
				                		 sta.setDatatype(2);
				                	 }
				                 }
				                 if(null != memo){
				                	 sta.setMemo(getCell(memo)); 
				                 }
								 DBMgr.getDbUtilInst().update("delete from dnms_datamine",null); 
							     magazineService.saveObject(sta);
		                 }
		                 temp ++ ; 
		            } 
				}
			} catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}  
	}
	public String getCell(HSSFCell cell){
		if(cell==null)return "";
		switch(cell.getCellType()){
			case HSSFCell.CELL_TYPE_NUMERIC:
				return (long)cell.getNumericCellValue() + "";
			case HSSFCell.CELL_TYPE_STRING:
				return cell.getStringCellValue();
			case HSSFCell.CELL_TYPE_FORMULA:
				return cell.getCellFormula();
			case HSSFCell.CELL_TYPE_BLANK:
				return "";
			case	HSSFCell.CELL_TYPE_BOOLEAN:
				return cell.getBooleanCellValue() + "";
			case HSSFCell.CELL_TYPE_ERROR:
	    	    return cell.getErrorCellValue() + "";
	    	default:
	    		return "";
		}
	}
	 /**
	  * 数据导出
	  * @param request
	  * @param response
	  * @return
	 * @throws Exception 
	  */
	@RequestMapping("/statics/exportData.do")   
	public ModelAndView exportData(HttpServletRequest request,
			HttpServletResponse response) throws Exception { 
		try  {
			String path = this.exportData();
			System.out.println("message::" + path );
			//  path是指欲下载的文件的路径。 
			response.setCharacterEncoding("UTF-8");
			File file  =   new  File(path);
			String filename  =  file.getName();
			int fileLength = (int)file.length();
			int                 length   = 0;
	        ServletOutputStream op       = response.getOutputStream();
	        ServletContext      context  = request.getSession().getServletContext();
	        String              mimetype = context.getMimeType( filename );
	        byte[]   byteContents   =   new   byte[fileLength];   
	        FileInputStream fis   =   new   FileInputStream(file);   
	        int   retcd   =   fis.read(byteContents);   
	        fis.close();    
	        logger.debug("mimetype:" + mimetype);
	        response.setContentType(mimetype == null?"application/vnd.ms-excel":mimetype );
	        response.setContentLength( retcd );
	        response.setHeader( "Content-Disposition", "attachment; filename=\"" + new String(filename.getBytes("gb2312"),"ISO-8859-1") + "\"" );
	        op.write(byteContents,0,retcd); 
	        op.close();
    }  catch  (IOException ex) {
        ex.printStackTrace();
    }
		return null;  
	} 
	/**
	 * 生成题录数据文件
	 * @return 生成的文件的路径
	 */
	public String exportData(){
		String message = "false";
		 try {
				HSSFWorkbook wb = new HSSFWorkbook(); 
				 HSSFSheet sheet = wb.createSheet("new sheet"); 
				 HSSFRow row = sheet.createRow((short)0); 
				 row.createCell(0).setCellValue("序号"); 
				 row.createCell(1).setCellValue("名称"); 
				 row.createCell(2).setCellValue("支持度"); 
				 row.createCell(3).setCellValue("置信度"); 
				 row.createCell(4).setCellValue("数据类型"); 
				 row.createCell(5).setCellValue("备注"); 
				 CellStyle cs=wb.createCellStyle();  
				 List accList = DBMgr.getDbUtilInst().queryList("select a.id,a.content,a.zhichidu,a.zhixingdu,b.dictname,a.memo from dnms_datamine a,hs_code b" +
				 		" where a.datatype=b.dictcode and b.dicttype='17' order by b.id",null);
				 for(int i=0; i<accList.size(); i++){
						Map data = (Map)accList.get(i);
						HSSFRow onerow = sheet.createRow((short)(i+1)); 
						onerow.createCell(0).setCellValue(String.valueOf(data.get("id"))); 
						onerow.createCell(1).setCellValue((String)data.get("content")); 
						onerow.createCell(2).setCellValue(String.valueOf(data.get("zhichidu")+"")); 
						onerow.createCell(3).setCellValue(String.valueOf(data.get("zhixingdu")+"")); 
						onerow.createCell(4).setCellValue(String.valueOf(data.get("dictname")+"")); 
						onerow.createCell(5).setCellValue((String)data.get("memo")); 
				 }	
				 String tempFilepath = SystemUtil.getWebappRoot().toString();
				 tempFilepath = tempFilepath.replace("\\", "/") + "/temp";
				 System.out.println("tempFilepath::" + tempFilepath );
				 File tempfile = new File(tempFilepath);
				 if(!tempfile.exists()){
					 tempfile.mkdirs();
				 }
				 String destfile = tempFilepath + "/" + "数据挖掘.xls";
				 FileOutputStream fileOut = new FileOutputStream(destfile); 
				 wb.write(fileOut); 
				 fileOut.close();
				 message = destfile;
			} catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} 
		return message;
		
	}
	/**
	  * 期刊种类详细信息
	  * @param request
	  * @param response
	  * @param modelMap
	  * @return
	  * @throws ServletException
	  * @throws IOException
	  */
	 @RequestMapping(value="/static/magatype.do")   
  public ModelAndView magatype(HttpServletRequest request,
				HttpServletResponse response,ModelMap modelMap) throws ServletException, IOException {
		String yearstr = request.getParameter("yearstr"); 
		String perPageCount = request.getParameter("perPageCount");
		StringBuffer buffer = new StringBuffer();
		buffer.append("select * from (select PERIODICALNAME,count(*) as countall from DNMS_MAGAZINE where year is not null and substr(year,0,4)='"+yearstr+"' group by PERIODICALNAME) order by countall desc ");
		String pagesql = request.getContextPath()+"/static/magatype.do?yearstr="+yearstr; 
		QueryPage qp = new QueryPage(request,pagesql,buffer.toString()); 
		if(null == perPageCount || "".equals(perPageCount)){
			qp.setPerPageCount(15);
		}else {
			qp.setPerPageCount(Integer.parseInt(perPageCount));
		}
	     qp.query();  
	     String pagenum =  qp.getCurrentPageNo() + "";
	     String pagecount = qp.getPerPageCount() + "";
	     modelMap.put("pagenum", pagenum);
	     modelMap.put("pagecount", pagecount);
	     modelMap.put("pagestr", qp.getPageStr());
	     modelMap.put("datalist", qp.getDataList());
	     //modelMap.put("allsql", countsql);
		return new ModelAndView("app/statics/magadetail",modelMap);
		 
	 } 
	 /**
	  * 期刊统计详细信息
	  * @param request
	  * @param response
	  * @param modelMap
	  * @return
	  * @throws ServletException
	  * @throws IOException
	  */
	 @RequestMapping(value="/static/magadetail.do")   
   public ModelAndView magadetail(HttpServletRequest request,
				HttpServletResponse response,ModelMap modelMap) throws ServletException, IOException {
		String yearstr = request.getParameter("yearstr"); 
 		String perPageCount = request.getParameter("perPageCount");
 		StringBuffer buffer = new StringBuffer();
 		buffer.append("select * from DNMS_MAGAZINE where year is not null and substr(year,0,4)='"+yearstr+"' order by maganame");
		String pagesql = request.getContextPath()+"/static/magadetail.do?yearstr="+yearstr; 
		QueryPage qp = new QueryPage(request,pagesql,buffer.toString()); 
		if(null == perPageCount || "".equals(perPageCount)){
			qp.setPerPageCount(8);
		}else {
			qp.setPerPageCount(Integer.parseInt(perPageCount));
		}
	     qp.query();  
	     String pagenum =  qp.getCurrentPageNo() + "";
	     String pagecount = qp.getPerPageCount() + "";
	     modelMap.put("pagenum", pagenum);
	     modelMap.put("pagecount", pagecount);
	     modelMap.put("pagestr", qp.getPageStr());
	     modelMap.put("datalist", qp.getDataList());
	     //modelMap.put("allsql", countsql);
		return new ModelAndView("app/statics/staticdetail",modelMap);
		 
	 } 
	 /**
	  * 详细信息
	  * @param request
	  * @param response
	  * @param modelMap
	  * @return
	  * @throws ServletException
	  * @throws IOException
	  */
	 @RequestMapping(value="/static/staticdetail.do")   
    public ModelAndView dataList(HttpServletRequest request,
				HttpServletResponse response,ModelMap modelMap) throws ServletException, IOException {
		String countsql = request.getParameter("countsql");
		String detailname = request.getParameter("detailname");
 		countsql = countsql.toLowerCase();
		countsql = countsql.substring(countsql.indexOf("from"),countsql.length());
		countsql = "select c.* " + countsql;
		detailname = "'" + detailname + "'";
		countsql = countsql.replace("?", detailname);
		
		countsql = countsql.replaceAll("&", "'");
		System.out.println("countsql::::" + countsql);  
		String orderName = request.getParameter("orderName");
		 String orderNum = request.getParameter("orderNum");
		 StringBuffer orderByStr = new StringBuffer(" order by ");
		 if(StringUtils.isNotBlank(orderName)){
		    	if("1".equals(orderNum)){
		    		orderByStr.append(orderName).append(" asc");
		    	}
		    	if("2".equals(orderNum)){
		    		orderByStr.append(orderName).append(" desc");
		    	}
		    }else{
		    	orderByStr.append("substr(c.year,0,4)").append(" desc");
		    	orderName = "substr(c.year,0,4)";
		    	orderNum = "2";
		    }
		 request.setAttribute("orderName", orderName); 
		 request.setAttribute("orderNum", orderNum); 
		 Map param = new HashMap();
		 param.put("orderName", orderName); 
		 param.put("orderNum", orderNum); 
		String perPageCount = request.getParameter("perPageCount");
		countsql+=orderByStr.toString();
		String pagesql = request.getContextPath()+"/static/staticdetail.do?countsql="+countsql + "&detailname=" + detailname; 
		QueryPage qp = new QueryPage(request,pagesql,countsql,param); 
		 
	     qp.query();  
	     String pagenum =  qp.getCurrentPageNo() + "";
	     String pagecount = qp.getPerPageCount() + "";
	     modelMap.put("pagenum", pagenum);
	     modelMap.put("pagecount", pagecount);
	     modelMap.put("pagestr", qp.getPageStr());
	     modelMap.put("datalist", qp.getDataList());
	     modelMap.put("allsql", countsql);
		return new ModelAndView("app/statics/staticdetail",modelMap);
		 
	 } 
	 private String getStaticStr(HttpServletRequest request,String ranggesql,String desc){
		 StringBuffer buffer = new StringBuffer();
		 DBMgr dbo = this.getDbo(request);
		 List paperlist = dbo.queryList(ranggesql, null); 
		 //证候
		 String symsql = "  SELECT symptomname FROM dnms_sym WHERE SYMPTOMNAME IS NOT NULL and SYMPTOMNAME !='无' and disid in(select id from DNMS_DISEASE where indexid in("+ranggesql+")) GROUP BY symptomname    ";
		 //方剂
		 String presql = "  SELECT prename FROM dnms_pre WHERE prename !='无' and prename IS NOT NULL and SYMID in(select id from DNMS_SYM where disid in(select id from DNMS_DISEASE where indexid in("+ranggesql+"))) GROUP BY prename  ";
		 //中药
		 String herbsql = " SELECT herbname  FROM dnms_herb WHERE herbname !='无' and herbname IS NOT NULL and preid in(select id from DNMS_PRE where SYMID in(select id from DNMS_SYM where disid in(select id from DNMS_DISEASE where indexid in("+ranggesql+")))) GROUP BY herbname";
		 //穴位
		 String pointsql = "SELECT   pointname FROM dnms_point WHERE pointname!='无' and pointname IS NOT NULL and PGROUPID in(select id from DNMS_POINTGROUP where SYMID in(select id from DNMS_SYM where disid in(select id from DNMS_DISEASE where indexid in("+ranggesql+")))) GROUP BY pointname";
		 List symlist = dbo.queryList(symsql, null);
		 List prelist = dbo.queryList(presql, null);
		 List herblist = dbo.queryList(herbsql, null);
		 List pointlist = dbo.queryList(pointsql, null);
		 buffer.append(desc).append("趋势分析中,");
		 buffer.append("共有").append(symlist.size()).append("个证候,");
		 buffer.append("涉及").append(paperlist.size()).append("篇文献,");
		 buffer.append(prelist.size()).append("个方剂,");
		 buffer.append(herblist.size()).append("个药物,");
		 buffer.append(pointlist.size()).append("个穴位");
 		 return buffer.toString();
	 }
}
